Hierarchical data is naturally present in many aspects of business operations. For example, hierarchical data typically models both the reporting and geographical relationships between employees. Hierarchical data may be represented using tree and graph structures. However, due to the flat nature of the traditional relational model, handling hierarchical data using Structured Query Language (SQL) statements is difficult and inefficient.
Logic for hierarchy handling is therefore typically written into an application which runs within an application server. In most cases, hierarchies are represented in a database schema using a simple relational encoding, and converted into a custom-tailored format within the application, if needed. The most widespread encoding is a self-referencing table resembling an adjacency list. FIG. 1 is an example of such a table, which encodes the hierarchy of FIG. 2. Field id uniquely identifies each item in the hierarchy, and the hierarchical relationships are established by a self-reference pid associating each item with its respective parent item.
In order to utilize adjacency lists as shown in FIG. 1, conventional SQL-responsive database systems use Recursive Common Table Expressions (RCTEs) (see. e.g., S. J. Finkelstein, N. Mattos, I. Mumick, and H. Pirahesh, “Expressing recursive queries in SQL,” ANSI Document X3H2-96-075r1, 1996) and techniques to define custom stored procedures. These tools exhibit usability and performance deficiencies, so the primary conventional alternative is to abandon the adjacency list model and implement a hierarchy encoding scheme manually, either on the relational level or within the application. Accordingly, this alternative lacks query engine support.